<?php
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;

class DefaultDb_Repositories_ScheduleRepository extends EntityRepository
{

    public function getNextSchedulesByRouteAndPointBranch($userId, $pointIdOrigin, $limit = 3, $recurrent = true)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $stringRecurrent=$recurrent===false?'':' OR s.recurrent = 1 ';
        $query = '
                SELECT s.*
                FROM schedule s
                INNER JOIN route_points rp ON rp.route_id = s.route_id
                #INNER JOIN users u ON u.point_id = rp.point_id
                INNER JOIN branches_user u ON u.point_id = rp.point_id 
                WHERE 
                ( 
                TIMESTAMP(s.start_date) > DATE(NOW()) 
                ' . $stringRecurrent . '
                )
                    AND u.client_id = '.$userId.'
                    AND rp.point_id = '.$pointIdOrigin.'
                    AND rp.status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '
                    AND s.status = ' . DefaultDb_Entities_Schedule::STATUS_ACTIVE . '
                ORDER BY s.start_date 
                LIMIT '.$limit.'
            ';
  //      echo $query;
        $res = $conn->executeQuery($query);
        $array = $res->fetchAll();        
        return $array;
    }
    /**
     * 
     * @param type $userId
     * @param type $pointIdOrigin
     * @param DateTime $date
     * @param type $limit
     * @param type $recurrent
     * @return type
     */
    public function getNextSchedulesByRouteAndPointBranchAndDate($userId, $pointIdOrigin, $date,$limit = 3, $recurrent = true)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $stringRecurrent=$recurrent===false?'':' OR s.recurrent = 1 ';
        $query = '
                SELECT s.*
                FROM schedule s
                INNER JOIN route_points rp ON rp.route_id = s.route_id
                INNER JOIN branches_user u ON u.point_id = rp.point_id 
                WHERE 
                ( 
                DATE(s.start_date) >= DATE(\''.$date->format('Y-m-d').'\') 
                ' . $stringRecurrent . '
                )
                    AND u.client_id = '.$userId.'
                    AND rp.point_id = '.$pointIdOrigin.'
                    AND rp.status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '
                ORDER BY s.start_date 
                LIMIT '.$limit.'
            ';
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return $res;
    }
    
    
    public function getNextSchedulesByPointId($pointId) {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
        SELECT s.*
	FROM schedule s
	INNER JOIN route_points rp ON rp.route_id = s.route_id
	WHERE date(s.start_date) > date(NOW())
		AND rp.point_id = ' . $pointId .'
	ORDER BY s.start_date';
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return $res;
    }
    
    public function getNextSchedulesByRouteByDate($route,$date) {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
        SELECT s.*
	FROM schedule s
	WHERE 
        ( timestamp(s.start_date) >= timestamp(\''.$date->format('Y-m-d H:i:s').'\')
                OR s.recurrent = 1 )
		AND s.route_id = '.$route->getId().'
                AND s.status = ' . DefaultDb_Entities_Schedule::STATUS_ACTIVE . '
	ORDER BY s.start_date';
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return $res;
    }

    /**
     * Se calcula el tiempo a parti del siguiente algoritomo, SE REGRESA EN MILISEGUNDOS.
     * 1. se obtienen todos los puntos de mi ruta, ordenados hasta mi punto de distribucion ($myPointOrder)
     * 2. de ests puntos se suma el tiempo (NO se toma en cuenta el punto 1, porque el tiempo indica del ultimo punto hacia el )
     * 3. se cuentan cuantos puntos son hacia mi punto y se multiplica por la constante de tiempo de configuracion.
     *
     * @param integer $routeId
     * @param integer $myPointOrder
     * @param integer $orderInicial Se Utiliza para sabaer en que orden esta el punto inicial ya que no siempre es el 1
     * @return integer
     */

    public function calculeShippidTime($routeId,$myPointOrder,$orderInicial=1)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();

        $query = '
                SELECT # * # calculando el bussness
                    time + (numPoints * secondsPerPoint) timeInSeconds
                FROM (
                    SELECT SUM(time) time
                    FROM (
                        SELECT TIME_TO_SEC(arrival_time) time
                        FROM route_points
                        WHERE route_id = '.$routeId.'
                            # se descarta el punto inicial
                            AND order_number <> '.$orderInicial.'
                            AND order_number <= '.$myPointOrder.'
                            AND status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL .'    
                        ORDER BY order_number
                        ) time
                ) time
                INNER JOIN
                (
                    SELECT count(id) numPoints
                    FROM route_points
                    WHERE route_id = '.$routeId.'
                        # se descarta el punto inicial y mi punto
                        AND order_number <> '.$orderInicial.'
                        AND order_number < '.$myPointOrder.'
                        AND status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL .'
                    ORDER BY order_number
                ) numPoints
                INNER JOIN
                (
                    SELECT minutes_per_point * 60  secondsPerPoint
                    FROM configurations
                    WHERE id = 1
                ) timePerPoint
            ';
        
        $res = $conn->executeQuery($query);
        $res = $res->fetch();
        
        return $res;
    }

    public function deleteSheduleDate($scheduled)
    {
        $em = $this->getEntityManager(); 
        $em->remove($scheduled);         
        $em->flush();
    }
    
     
    /**
     * Regresa el centro de intercambio donde se intersectan dos rutas, y el orden maximo de esa ruta
     * para posteriormente calcular el tiempo que tarda en dar la vuelta completa una ruta
     * @param type $route
     * @param type $secondRoute
     * @return type
     */
    public function getIntersectionAndMaxRoutePointExchangeCenter($route,$secondRoute)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
         #SELECCIONA LA INTERSECCION DE DOS RUTAS Y REGRESA EL PUNTO MAXIMO DE LA PRIMERA RUTA
        SELECT A.idpoint,A.name,A.type,A.idroutepoint,A.route_id,max(A.idroutepoint) AS max_id_routepoint from 
        (
                SELECT * FROM (
                        SELECT id AS idpoint,name,type from points WHERE type = '.DefaultDb_Entities_Point::TYPE_EXCHANGE_CENTER.'
                        AND status = ' . DefaultDb_Entities_Point::STATUS_NORMAL . '
                        ) pa 
                LEFT JOIN (
                        SELECT id AS idroutepoint,route_id,point_id,order_number FROM route_points
                        WHERE route_id ='.$route->getId().'
                        AND status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '    
                        ) rta
                ON pa.idpoint = point_id WHERE rta.route_id ='.$route->getId().'
        ) A
        INNER JOIN 
        (
                SELECT * FROM (
                        SELECT id AS idpoint,name,type from points WHERE type = '.DefaultDb_Entities_Point::TYPE_EXCHANGE_CENTER.'
                        AND status = ' . DefaultDb_Entities_Point::STATUS_NORMAL . '
                        ) pa 
                LEFT JOIN (
                        SELECT id as idroutepoint,route_id,point_id,order_number FROM route_points
                        WHERE route_id ='.$secondRoute->getId().'
                        AND status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '   
                        ) rta
                ON pa.idpoint = point_id WHERE rta.route_id ='.$secondRoute->getId().'
        )
        B
        ON A.idpoint = B.idpoint LIMIT 0,1';
        $res = $conn->executeQuery($query);
        $res = $res->fetch();
        return $res;
        
    }
    
    /**
     *  No esta siendo utilizado
     * @param type $route
     * @return type
     */
    public function getNextRoute($route)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
        SELECT DISTINCT route_id 
        FROM route_points 
        WHERE point_id = (SELECT DISTINCT idpoint FROM (
                SELECT id AS idpoint,name,type from points WHERE type = '.DefaultDb_Entities_Point::TYPE_EXCHANGE_CENTER.'
                ) pa 
            LEFT JOIN (
                SELECT id AS idroutepoint,route_id,point_id,order_number FROM route_points
                WHERE route_id ='.$route->getId().'
                ) rta
            ON pa.idpoint = point_id WHERE rta.route_id ='.$route->getId().')
        and route_id <> '.$route->getId();
        
        $res = $conn->executeQuery($query);
        $res = $res->fetch();
        return $res;  
    }
    
    /**
     * Regresa un schedule por usuario y fecha del schedule
     * @param DefaultDb_Entities_User $user Usuario a buscar
     * @param dateTime $date Fecha a buscar 
     * @return mixed Regresa un schedule o null
     */
    public function getScheduleByUserAndDate($user,$date) {
        $em = $this->getEntityManager();      
        $dql = '
        SELECT s
        FROM DefaultDb_Entities_Schedule s 
        WHERE s.user = '.$user->getId().'
        AND( s.startDate BETWEEN \''.$date->format('Y-m-d 00:00:00').'\' AND\''.$date->format('Y-m-d 23:59:59').'\' )
        AND s.status = '.DefaultDb_Entities_Schedule::STATUS_ACTIVE.'
	ORDER BY s.startDate ';
        $query = $em->createQuery($dql)->setMaxResults(1);
        $res = $query->getOneOrNullResult();
        return $res;
    }
}